TABLE OF CONTENTS


Our logo showing our story of the brand without any words.


99excel.com



  1. Introduction to Microsoft Excel VBA


    Lesson 1: Introduction to Excel VBA

    VBA is the acronym for Visual Basic for Applications. It is an integration of the Microsoft’s event-driven programming language Visual Basic with Microsoft Office applications such as Microsoft Excel. Excel VBA means Excel Visual Basic for Applications which can be created using the built-in Visual Basic Editor in Microsoft Excel, and you can use it to customize and extend the capabilities of MS Excel. An application created by Excel VBA is also known as macro. Our tutorial is based on MS Excel 2003. We shall show you how to create Excel VBA in easy steps.


    There are two methods which you can create an Excel VBA. The first is to place a command button on the MS Excel spreadsheet and click on the button to enter the Visual Basic Editor. The second is to launch the Visual Basic Editor from the menu. To launch the VBE, click on tools on the menu bar and select macro and then click on Visual Basic Editor.


      1. reating Excel VBA Using the Command Button


        To create an Excel VBA using the command button, you need to place it on the spreadsheet. In order to access the command button, you need to click View on the MS Excel menu bar and then click on the toolbar and select the Control Toolbox to launch the control toolbox, as shown in Figure 1.1 .The control toolbox comprises various controls, as shown in Figure 1.2. Select the command button and place it on the spreadsheet.


        http://www.vbtutor.net/Images/progra_jan_21_2008_vba1.gif


        Figure 1.1


        http://www.vbtutor.net/Images/progra_jan_21_2008_vba2.gif


        >


        Figure 1.2: The Control Toolbox


        Next, you click on the command button and the Visual Basic Editor will appear. Enter the statement as shown in the figure. The first statement will fill up cell A1 to cell A10 with the phrase "Visual Basic" while the second statement add the value in cell A11 and cell B11 and then show the sum in cell C11. It is that simple.


        http://www.vbtutor.net/Images/progra_jan_21_2008_vba3.gif

        The Output


        http://www.vbtutor.net/Images/progra_jan_21_2008_vba4.gif


      2. reating Excel VBA in Visual Basic Editor


    To launch the VBE, click on tools on the menu bar and select macro and then click on Visual Basic Editor,as shown in Figure 1.3. The Visual Basic Editor code window is shown in Figure 1.4. You can create a VBA function in the VBE. We will learn about function in later lesson.


    http://www.vbtutor.net/vb6/excelimg1.jpg


    Figure 1.3


    http://www.vbtutor.net/vb6/excelimg2.jpg


    Figure 1.4: Visual Basic Editor Code Window

    Lesson 2: Working with Variables in Excel VBA


      1. The Concept of Variables


        Variables are like mail boxes in the post office. The content of the variables changes every now and then, just like the mail boxes. In Excel VBA, variables are areas allocated by the computer memory to store data. Like the mail boxes, each variable must be given a name.


        The following are the rules when naming the variables in Excel VBA


        • They must not exceed 40 characters

        • They must contain only letters, numbers and underscore chacaters

        • No spacing is allowed

        • It must not begin with a number

        • Period is not permitted


          Examples of valid and invalid variable names are displayed in Table 2.1


          Valid Name

          Invalid Name

          My_Car

          My.Car

          ThisYear

          1NewBoy

          Long_Name_Can_beUSE

          He&HisFather

          *& is not acceptable

          Group88

          Student ID

          * Spacing not allowed


          Table 2.1 : Example of valid and invalid variable names


      2. Data Types


    Excel VBA data types can be grossly divided into two types, the numeric data types and non-numeric data types. They are classified below:


        1. Numeric Data Types


          Numeric data types are types of data that consist of numbers, which can be computed mathematically with various standard aritmetic operators such as add, minus, multiply, divide and so on. In Excel VBA, the numeric data are divided into 7 types, which are summarized in Table 2.2


          https://99Excel.Com/vba_img/figure_L2.1.jpg


          Table 2.2: Numeric Data Types


        2. Non-numeric Data Types


    The nonnumeric data types are summarized in Table 2.3


    Data Type

    Storage

    Range

    String(fixed length)

    Length of string

    1 to 65,400 characters

    String(variable length)

    Length + 10 bytes

    0 to 2 billion characters

    Date

    8 bytes

    January 1, 100 to December 31, 9999

    Boolean

    2 bytes

    True or False

    Object

    4 bytes

    Any embedded object

    Variant(numeric)

    16 bytes

    Any value as large as Double

    Variant(text)

    Length+22 bytes

    Same as variable-length string

    Table 2.3: Nonnumeric Data Types


      1. Declaration of variables


        In Excel VBA, we needs to declare the variables before using them by assigning names and data types. You can declare the variables implicitly or explicitly.


        1. Implicit Declaration

          We can use a variable without openly(explicitly) declare it if we assign an initial value to it. For example,


          MyFirstName="John" MyAge=32

          Excel VBA automatically create two varaibles MyFirstName and MyAge as variants, and they are assigned data as John and 32 respectively. This type of declaration is called implicit declaration.


        2. Explicit Declaration


    Implicit declaration of variable often lead to errors in writing code therefore it is better to declare a variable explicitly. Variables are normally declared in the general section of the codes' window using the Dim statement.


    The syntax is as follows:


    Dim variableName as DataType


    Example 2.1


    Dim


    password


    As


    String

    Dim

    yourName

    As

    String

    Dim

    firstnum

    As

    Integer

    Dim

    secondnum

    As

    Integer

    Dim

    total

    As

    Integer

    Dim BirthDay As Date


    You may also combine them in one line, separating each variable with a comma, as follows:


    Dim password As String, yourName As String, firstnum As Integer.


    If the data type is not specified, Excel VBA will automatically declare the variable as a Variant. For string declaration, there are two possible formats, one for the variable-length string and another for the fixed-length string. For the variable-length string, just use the same format as Example 2.1 above. However, for the fixed- length string, you have to use the format as shown below:


    Dim VariableName as String * n


    where n defines the number of characters the string can hold. For example, Dim yourName as String * 10 mean yourName can hold no more than 10 Characters.


    Example 2.2


    In this example, we declared three types of variables, namely the string, date and currency.


    Private Sub CommandButton1_Click() Dim YourName As String

    Dim BirthDay As Date Dim Income As Currency

    YourName = "Alex" BirthDay = "1 April 1980"

    Income = 1000 Range("A1") = YourName Range("A2") = BirthDay Range("A3") = Income End Sub


    Lesson 3: Array in Excel VBA


      1. What is an Array?


        When we work with a single item, we only need to use one variable. However, if we have a list of items which are of similar type to deal with, we need to declare an array of variables instead of using a variable for each item. For example, if we need to enter one hundred names, instead of declaring one hundred different variables, we need to declare only one array. By definition, an array is a group of variables with the same data type and name. We differentiate each item in the array by using subscript, the index value of each item, for example name (1), name (2), name (3) .......etc.


      2. Declaring Arrays in Excel VBA


        We use the Dim statement to declare an array just as the way we declare a single variable.In Excel VBA, we can have a one dimensional array, two dimensional array or even a multidimensional array (up to 60)


        logo

        1. One Dimensional Array


          The general statements to declare a one dimensional array in Excel VBA is as follows: Dim arrayName(index) as dataType or

          Dim arrayName(first index to last index) as dataType For example,

          Dim StudentName(10) as String Dim StudentName(1 to 10) as String

          Dim StudentMark(10) as Single


          Dim StudentMark( 1 to 10) as Single Example 3.1

          In this example, we define an array StudentName of five strings using the Dim keyword. We include an InputBox to accept input from the user. We also use the For ...Next loop to accept the input five times and display the five names from cell A1 to cell E1. The code is as follows:


          Private Sub CommandButton1_Click( )


          Dim

          StudentName(1

          to

          5)

          As

          String

          For

          i

          =

          1

          To

          5

          StudentName(i) = InputBox("Enter student Name")

          Cells(i, 1) = StudentName(i) Next


          End Sub


          ** You can also declare the array using Dim StudentName(5) As String


          When we run the program, an input box will appear, as shown below. This input box will repeat five times and let the user enter five names.


          https://99Excel.Com/vba_img/example21.1.jpg


          The five names will be displayed in the spreadsheet as shown below:


          logo

          https://99Excel.Com/vba_img/example21.1.2.jpg


          Example 3.2


          You can also declare more than one array in a single line. In this example, we declare three arrays in a single line, separated by commas.


          Private Sub CommandButton1_Click( )


          Dim StudentName(3) As String, StudentID(3) As String, StudentMark(3) As Single For i = 1 To 3

          StudentName(i) = InputBox("Enter student Name") StudentID(i) = InputBox("Enter student ID") StudentMark(i) = InputBox("Enter student Mark") Cells(i, 1) = StudentName(i)

          Cells(i, 2) = StudentID(i) Cells(i, 3) = StudentMark(i) Next


          End Sub


          When we run the program, three input boxes will appear consecutively to let the user enter the student name, the student ID and then the student mark. The process will repeat three times until the particulars of all three students have been entered. The three input boxes and the output are shown below:


          https://99Excel.Com/vba_img/example21.1.jpg


          https://99Excel.Com/vba_img/example21.1.3.jpg


          https://99Excel.Com/vba_img/example21.1.4.jpg


          https://99Excel.Com/vba_img/example21.1.5.jpg


        2. Two Dimensional Array


    Multidimensional arrays are often needed when we are dealing with more complex database, especially those that handle large amount of data. Data are usually organized and arranged in table form, this is where the multidimensional arrays come into play. However, in this tutorial, we are dealing only with the two dimensional array. Two dimensional array can be represented by a table that contains rows and columns, where one index represents the rows and the other index represent the columns.


    The format to declare a two dimensional array is


    Dim arrayName (num1, num2) as datatype


    Where num1 is the suffix of the first dimension of the last element and num2 is the suffix of the second dimension of the last element in the array. The suffixes of the element in the array will start with (0, 0) unless you set the Option Base to 1. In the case when the Option Base is set to 1, then the suffixes of the element in the array will start with (1, 1). For example,


    Dim Score (3, 3) as Integer


    will create a two dimension array consists of 16 elements. These elements can be organized in a table form as shown in the table below:

    logo


    logo


    Score(0,0)


    Score(0,1)


    Score(0,2)


    Score(0,3)


    Score(1,0)


    Score(1,1)


    Score(1,2)


    Score(1,3)


    Score(2,0)


    Score(2,1)


    Score(2,2)


    Score(2,3)


    Score(3,0)


    Score(3,1)


    Score(3,2)


    Score(3,3)


    If you set the option base to 1, then there will be only 9 elements, i.e from Score(1,1) to Score(3,3). However, if you want the first element to start with suffixes (1,1) you can also use the following format of declaration:


    Dim Score(1 to 3, 1 to 3) as Integer


    Example 3.3


    If a company wants to track the performance of 5 salespersons over a period of 2 days, you can create a 5x2 array in Excel VBA, denoted by a 5X 2 table in a spreadsheet.


    You can write the following VBA code: Private Sub CommandButton1_Click() Dim SalesVolume(2to 6, 2 to 3) as Single

    Dim SalesPerson as Integer, Day as Integer For SalesPerson=2 to 6

    For Day=2 to3


    SalesVolume(SalesPerson, Day)=inputbox("Enter Sales Volume") Cells(SalesPerson, Day)=SalesVolume(SalesPerson,Day)

    Next Day


    Next SalesPerson End Sub

    When the program is run, the inputbox that will prompt the user to enter sales volume will appear 10 times, as shown below:


    https://99Excel.Com/vba_img/example21.3.1.jpg


    After all the sales Volumes are entered, the values in the spreadsheet are shown below:


    https://99Excel.Com/vba_img/example21.3.2.jpg

    If you need to make sure the user enters the correct sales volume, you can change line 5 statement to SalesVolume(SalesPerson, Day) = InputBox("Enter Sales Volume of " & " SalesPerson " & (SalesPerson - 1) &


      1. Arithmetic Operators


        Arithmetic operators are used to perform mathematical operations in Excel VBA.



        Operator


        Mathematical function


        Example


        ^


        Exponential


        MsgBox 2^4 gives a value of 16


        *


        Multiplication


        MsgBox 4*3 gives a value of 12,


        /


        Division


        MsgBox 12/4 gives a value of 3


        Mod


        Modulus (returns the remainder from an integer division)


        MsgBox 15 Mod 4 gives value of 3


        \


        Integer Division(discards the decimal places)


        MsgBox 19\4 gives a value of 4


        + or &


        String concatenation


        MsgBox "Excel"&"VBA 2010" or "Excel"+"VBA 2010" produces a new string "Excel VBA 2010"


        MsgBox is a built-in function of excel VBA that displays a message. We shall learn more about functions in next lesson. Note that MsgBox 1+"VBA" will produce a type mismatch error whereas MsgBox 1&"VBA" will not result in an error, it gives a concatenated string 1VBA. We shall engage the usage of arithmetic operators in Excel VBA code writing in future lessons.


      2. Comparison Operators


        Comparison


        If

        operators

        are

        often

        used

        in

        writing code


        mark>50

        that

        require

        decisions

        making.

        For

        example,


        then

        MsgBox

        "Pass"

        Else

        MsgBox

        "Fail"

        Endif



        Operator


        Meaning


        Example


        <


        Less than


        MsgBox 2<3 returns true while MsgBox 4>5 returns false


        <=


        Less than or equal to


        MsgBox 3<=4 returns true

        We shall learn more about writing decision making code in future lessons. Here are a list of comparison operators:



        >


        Greater than


        MsgBox 5>4 returns true


        >=


        Greater than or equal to


        MsgBox 10>=9 returns true


        =


        Equal to


        MsgBox 10=10 returns true


        <>


        Not Equal to


        MsgBox 9<>10 returns true


        * For letters, the hierarchy is A>B>C>..........>Z Therefore MsgBox A>B returns true


      3. Logical Operators


    Logical operators are also used in writing decision making codes by comparing values or expressions.



    Operator


    Meaning


    Example


    And


    Logical Conjunction


    If A>=80 And B<101 thenGrade="A"


    Or


    Logical Disjunction


    If income>5000 or car>2 thenStatus="Rich"


    Not


    Logical negation


    MsgBox Not (3 > 4)returns true


    Xor


    Similar to Or, except that it returns False if both camparison values are true


    MsgBox 4 > 3 Xor 5 >2 returns false


    Lesson 5: Subroutines and Functions


      1. : Subroutines


        A Subroutine in Excel VBA is a procedure that performs a specific task and to return values, but it does not return a value associated with its name. However, it can return a value through a variable name. Subroutines are usually used to accept input from the user, display information, print information, manipulate properties or perform some other tasks. It is a program code by itself and it is not an event procedure because it is not associated with a runtime procedure or an Excel VBA control such as a command button. It is called by the main program whenever it is required to perform a certain task. Sub procedures help to make programs smaller and easier to manage. A Subroutine begins with a Sub statement and ends with an End Sub statement.


        The program structure of a sub procedure is as follows:

        Sub subProg(arguments)


        Statements End Sub


        Subroutines are called by the main program using the Call keyword.


        Sub MainProg( )


        Call SubProg()


        End Sub


        A subroutine is different from a function that it does not return a value directly. You can include parameters in a subroutine.


        Example 5.1


        In this example, the main program calls the subroutine findHidden and execute it. The end result is a message box that display the hidden text.


        Private Call End Sub

        hidden_txt MsgBox End Sub

        Sub


        =

        CommandButton1_Click()

        findHidden

        Sub findHidden() "@#%43&*" hidden_txt


        Example 5.2


        Private Sub CommandButton1_Click() Call salary(10, 300)

        End Sub

        Sub salary(wage As Single, hours As Single) MsgBox wage * hours

        End Sub


        In this example, the Call command calls the subroutine salary and passes the parameters 10 and 300 to it. It will calculate the salary based on wage per hour and number of hours and display on the message box.

      2. Functions


        In Excel VBA, a function is similar to a subroutine but the main purpose of the function is to accept a certain input from the user and return a value which is passed on to the main program to finish the execution. There are two types of functions, the built-in functions (or internal functions) and the functions created by the programmers, or simply called user-defined functions. The first built-in function that we have already learned and familiar with its usage is the Message Box. We are not going to repeat here but we shall take a look at its syntax once more, i.e.


        message=MsgBox(Prompt, Style Value,Title)


        Now we shall examine the next commonly used function in Excel VBA, the InputBox function.


        1. InputBox function


          An InputBox( ) function displays a message box where the user can enter a value or a message in the form of text. The syntax is


          myMessage=InputBox(Prompt, Title, default_text, x-position, y-position)


          myMessage is a variant data type but typically it is declared as string, which accept the message input by the users. The arguments are explained as follows:


          • Prompt represents he message displayed normally as a question asked.

          • Title represents the title of the Input Box.

          • default-text represents the default text that appears in the input field where users can use it as his intended input or he may change to the message he wish to key in.

          • x-position and y-position represents the position or the coordinate of the input box.


    Example 5.1


    In this example, we insert a label and a command button into the MS Excel spreadsheet. Double click on the command button and enter the Excel VBA code as follows:


    Private Sub CommandButton1_Click()

    Dim userMsg As String

    userMsg = InputBox("What is your message?", "Message Entry Form", "Enter your messge here", 500, 700) If userMsg <> "" Then

    MsgBox( userMsg) Else

    MsgBox("No Message") End If

    End Sub



    Range("A1:C6").Columns(2).Font.ColorIndex = 3


    The output:


    image


    Figure 19.2


      1. The Rows Property


        Basically the syntax for the Rows property is similar to that of the Columns property, you just need to replace Columns with rows. The syntax of selecting a row within a certain range is


        Range(starting_cell,Ending_ Cell).Rows(i).Select


        Example 19.4


        This following code selects the third row within the range A1 to F3 Private Sub CommandButton2_Click() Range("A1:F3").Rows(3).Select

        End Sub


        image


        Figure 19.3 Example 19.5: Using With Range...End With for Rows‌


        Private Sub CommandButton1_Click() With Range("A1:F3").Rows(2)

        .Font.ColorIndex = 3


        .Font.Bold = True


        .Font.Italic = True


        .Font.Underline = True


        .Font.Name = "Times New Roman"


        .Font.Size = 14


        .Interior.Color = RGB(255, 255, 0) End With

        End Sub

        The Output


        image


        Figure 19.4‌


      2. Using the Set keyword to Declare Range


        We can write Excel VBA code that can specifies certain range of cells using the set keyword and then perform certain tasks according to a set of conditions. In Example 19.6, we shall write the ExcelVBA code such that it can accept range input from the user and then change the mark to blue if it is more than or equal to 50 and change it to red if the mark is less than 50.


        Example 19.6


        Private Sub CommandButton1_Click()


        Dim rng, cell As Range, selectedRng As String


        .selectedRng = InputBox("Enter your range")


        .Set rng = Range(selectedRng)


        .For Each cell In rng


        .If cell.Value >= 50 Then


        .cell.Font.ColorIndex = 5


        .Else


        .cell.Font.ColorIndex = 3

        .End If


        .Next cell


        .End Sub


        Explanation:


        The InputBox function is used to accept value from the users.


        rng and cell are declared as a Range variable using the Dim statement while selectedRng is declared as a string that receive input from the user. Once the input is obtained from the user, it is stored using the Set method and the Range function.


        For Each cell In rng...Net cell is a loop that can iterate through the selected range, one cell at a time. The If...Then...Else statements are to specify the color of the font according to the range of values determined by the conditions.


        The Output


        image


        Figure 19.5‌


      3. The Formula property


    You can use the Formula property of the Range object to write your own customized formula.

    Example 19.7


    Private Sub CommandButton1_Click() Range("A1:B3").Columns(3).Formula = "=A1+B1" End Sub

    In this example, the formula A1+B1 will be copied down column 3 (column C) from cell C1 to cell C3. The program automatically sums up the corresponding values down column A and column B and displays the results in column C, as shown in Figure 19.6


    image


    Figure 19.6


    The above example can also be rewritten and produces the same result as below: Range("A1:B3").Columns(3).Formula = "=Sum(A1:B1)"

    There are many formulas in Excel VBA which we can use to simplify and speed up complex calculations. The formulas are categorized into Financial, Mathematical, Statistical, Date ,Time and others. For example, in the statistical category, we have Average (Mean), Mode and Median


    Example 19.8


    In this example, the program computes the average of the corresponding values in column A and column B and displays the results in column C. For example, the mean of values in cell A1 and Cell B1 is computed and displayed in Cell C1. Subsequent means are automatically copied down Column C until cell C3.


    Private Sub CommandButton1_Click()


    Range("A1:B3").Columns(3).Formula = "=Average(A1:B1)"

    End Sub


    Example 19.9: Mode


    In this example, the program computes the mode for every row in the range A1:E4 and displays them in column

    F. It also makes the font bold and red in color, as shown in Figure 19.6. Private Sub CommandButton1_Click() Range("A1:E4").Columns(6).Formula = "=Mode(A1:E1)" Range("A1:E4").Columns(6).Font.Bold = True Range("A1:E4").Columns(6).Font.ColorIndex = 3

    End Sub


    The Output


    image


    Figure 19.7


    Lesson 20: The Worksheet Object


      1. The Worksheet Properties in Excel VBA


        Similar to the Range Object, the Worksheet has its own set of properties and methods. When we write Excel VBA code involving the Worksheet object, we use Worksheets.

        The reason is that we are dealing with a collection of worksheets most of the time, so using Worksheets enables us to manipulate multiple worksheets at the same time. Some of the common properties of the worksheet

        are name, count, cells, columns, rows and columnWidth.


        Example 20.1


        Private Sub CommandButton1_Click() MsgBox Worksheets(1).Name

        End Sub


        Running the code will produce a pop-up dialog that displays the worksheet name as sheet 1, as shown in Figure 19.1.


        https://99Excel.Com/wp-content/uploads/2013/03/vba_Figure16.1.jpg


        Figure 20.1


        The count property returns the number of worksheets in an opened workbook.


        Example 20.2


        Private Sub CommandButton1_Click() MsgBox Worksheets.Count

        End Sub


        The output is shown in Figure 20.2.


        https://99Excel.Com/wp-content/uploads/2013/03/vba_Figure16.2.jpg

        Figure 20.2‌


        Example 20.3


        The count property in this example will return the number of columns in the worksheet. Private Sub CommandButton1_Click()

        MsgBox Worksheets(1).Columns.Count End Sub

        The output is shown below:


        https://99Excel.Com/wp-content/uploads/2013/03/vba_Figure16.3.jpg


        Figure 20.3


        Example 20.4


        The count property in this example will return the number of rows in the worksheet. Private Sub CommandButton1_Click()

        https://99Excel.Com/wp-content/uploads/2013/03/vba_Figure16.4.jpg

        MsgBox Worksheets(1).Rows.Count End Sub


        Figure 20.4


      2. The Worksheet Methods


    Some of the worksheet methods are add, delete, select, SaveAs, copy, paste and more.

    Example 20.5


    Private Sub CommandButton1_Click()


    'Add a new worksheet Worksheets. Add End Sub

    Private Sub CommandButton2_Click()


    'Delete a worksheet Worksheets(1).Delete End Sub

    Example 20.6


    The select method associated with worksheet lets the user select a particular worksheet. In this example, worksheet 2 will be selected.


    Private Sub CommandButton1_Click() 'Worksheet 2 will be selected Worksheets(2).Select

    End Sub


    The select method can also be used together with the Worksheet抯 properties Cells, Columns and Rows as shown in the following examples.


    Example 20.7


    Private Sub CommandButton1_Click() 'Cell A1 will be selected

    Worksheets (1).Cells (1).Select End Sub

    Example 20.8


    Private Sub CommandButton1_Click()

    'Column 1 will be selected Worksheets (1).Columns (1).Select End Sub

    Example 20.9


    Private Sub CommandButton1_Click() 'Row 1 will be selected

    Worksheets (1).Rows (1).Select 'Row 1 will be selected

    End Sub


    Excel VBA 2010 also allows us to write code for copy and paste. Let抯 look at the following Example:


    Example 20.10


    Private Sub CommandButton1_Click() 'To copy the content of a cell 1 Worksheets(1).Cells(1).Select Selection.Copy

    End Sub


    Private Sub CommandButton2_Click() 'To paste the content of cell 1 to cell 2 Worksheets(1).Cells(2).Select ActiveSheet.Paste

    Lesson 21: The Workbook Object


    In previous lesson, we have learned to write code associated with the worksheet object in Excel VBA 2010. In this lesson, we shall learn about the Workbook object . The Workbook object at the top of the hierarchy of the Excel VBA 2010 objects. We will deal with properties and methods associated the Workbook object.


      1. The Workbook Properties.


        When we write Excel VBA code involving the Workbook object, we use Workbooks. The reason is that we are dealing with a collection of workbooks most of the time, so using Workbooks enables us to manipulate multiple workbooks at the same time. When will deal with multiple workbooks, we can use indices to denote different workbooks that are open, using the syntax Workbooks (i), where i is an index. For example, Workbooks (1) denotes Workbook1, Workbooks (2) denotes Workbook2 and more. Workbooks have a number of properties. Some of the common properties are Name, Path and FullName Let's look at the following example:


        Example 21.1


        Private Sub CommandButton1_Click() MsgBox Workbooks(1).Name

        End Sub


        The program will cause a message dialog box to pop up and displays the first workbook name, i.e. Book1 as shown in Figure 20.1 below:


        image


        Figure 21.1


        If we have only one open workbook, we can also use the syntax ThisWorkbook in place of Workbook (1), as follows:


        Private Sub CommandButton1_Click () MsgBox ThisWorkbook.Name

        End Sub


        Example 21.2


        Private Sub CommandButton1_Click () MsgBox ThisWorkbook.Path

        End Sub


        Or you can use the following code: Private Sub CommandButton1Click () MsgBox Workbooks ("Book1").Path End Sub

        image

        The Output is shown in Figure 21.2


        Figure 21.2


        Example 21.3


        This example will display the path and name of the opened workbook. The code is: Private Sub CommandButton1_Click ()

        MsgBox ThisWorkbook.FullName


        End Sub


        Or


        Private Sub CommandButton1Click() MsgBox Workbooks("Book1").Fullname End Sub

        image

        The output is shown in Figure 21.3.


        Figure 21.3


      2. The Workbook Methods


    There are a number of methods associated with the workbook object. These methods are Save, SaveAs, Open, Close and more.


    Example 21.4


    In this example, when the user clicks on the command button, it will open up a dialog box and ask the user to specify a path and type in the file name, and then click the save button, not unlike the standard windows SaveAs dialog, as shown in Figure 21.4.


    Private Sub CommandButton1_Click() fName = Application.GetSaveAsFilename ThisWorkbook.SaveAs Filename:=fName End Sub


    image


    Figure 21.4


    Another method associated with the workbook object is open. The syntax is Workbooks.Open ("File Name")

    Example 21.5


    In this example, when the user click on the command button, it wil open the file book1.xls under the path燙:\Users\Toshiba\Documents\Liew Folder\VBA\vba2010\


    Private Sub CommandButton1_Click()


    Workbooks.Open ("C:\Users\Toshiba\Documents\Liew Folder\VBA\vba2010\book1.xlsx") End Sub

    The close method is the command that closes a workbook. The syntax is Workbooks (i).Close

    Example 21.6


    In this example, when the user clicks the command button, it will close Workbooks (1). Private Sub CommandButton1_Click()

    Workbooks (1).Close End Sub


    Lesson 22: Working with Check Box and Option Button


      1. Check Box in Excel VBA


        In Excel VBA , the Check box is a very useful control . It allows the user to select one or more items by checking the check box or check boxes concerned. For example, you may create a shopping cart where the user can click on check boxes that correspond to the items they intend to buy, and the total payment can be computed at the same time. One of most important properties of the check box in Excel VBA 2010 is Value. If the check box is selected or checked, the value is true, whilst if it is not selected or unchecked, the Value is False.


        The usage of check box is illustrated in Example 22.1


        Example 22.1


        In this example, the user can choose to display the sale volume of one type of fruits sold or total sale volume. The code is shown below:


        Private Sub CommandButton1_Click()


        If CheckBox1.Value = True And CheckBox2.Value = False Then MsgBox "Quantity of apple sold is" & Cells (2, 2).Value

        ElseIf CheckBox2.Value = True And CheckBox1.Value = False Then MsgBox "Quantity of orange sold is " & Cells(2, 3).Value

        Else

        MsgBox "Quantity of Fruits sold is" & Cells (2, 4).Value End If

        End Sub


        The output Interface is shown in Figure 22.1


        image

        Figure 22.1‌


      2. Option Button


    The option button control also lets the user selects one of the choices. However, two or more option buttons must work together because as one of the option buttons is selected, the other option button will be deselected. In fact, only one option button can be selected at one time. When an option button is selected, its value is set to "True" and when it is deselected; its value is set to "False".


    Example 22.2


    This example demonstrates the usage of the option buttons. In this example, the Message box will display which option button selected by the user. The output interface is shown in Figure 22.2.


    The code

    Private Sub CommandButton1_Click() If OptionButton1.Value = True Then

    MsgBox "Option1 is selected"


    ElseIf OptionButton2.Value = True Then MsgBox "Option2 is selected"

    Else


    MsgBox "Option3 is selected" End If

    End Sub


    image

    The output


    Figure 22.2

    Lesson 23: Working with List Box, Combo Box and Toggle Button


    We have learned how to work with check boxes, option buttons and text boxes in Excel VBA in the previous lessons. We shall continue to learn how to manipulate other controls in Excel VBA 2010 in this lesson. In this lesson, we will deal with List Box, Combo Box and Toggle Button.


      1. List Box


        The function of the List Box is to present a list of items where the user can click and select the items from the list.

        To add items to the list, we can use the AddItem method. To clear all the items in the List Box, you can use the Clear method. The usage of Additem method and the Clear method is shown Example 23.1.


        Example 23.1


        Private Sub CommandButton1_Click() For x = 1 To 10

        L; istBox1.AddItem "Apple" Next

        End Sub


        'To clear the List Box


        Private Sub CommandButton2_Click() For x = 1 To 10

        ListBox1.Clear Next

        End Sub


        >


        image


        Figure 23.1


      2. Combo Box


        The function of the Combo Box is also to present a list of items where the user can click and select the items from the list. However, the user needs to click on the small arrowhead on the right of the combo box to see the items which are presented in a drop-down list. In order to add items to the list, you can also use the AddItem method.


        Example 23.2


        Private Sub CommandButton1_Click() ComboBox1.Text = "Apple"

        For x = 1 To 10 ComboBox1.AddItem "Apple" Next

        End Sub


        'To clear the combo box


        Private Sub CommandButton2_Click() ComboBox1.Clear

        End Sub


        image

        The Output


        Figure 23.2‌


      3. Toggle Button


    Toggle button lets the user switches from one action to another alternatively. When the Toggle button is being depressed, the value is true and when it is not depressed, the value is false. By using the If and Else code structure, we can thus switch from one action to another by pressing the toggle button repeatedly.

    Example 23.3


    In this example, the user can toggle between apple and orange as well as font colors. Private Sub ToggleButton1_Click ()

    If ToggleButton1.Value = True Then Cells (1, 1).Font.Color = vbRed Else

    Cells (1, 1) = "Orange"


    Cells (1, 1).Font.Color = vbBlue End If

    End Sub


    View the animated image in Figure 23.3


    https://99Excel.Com/wp-content/uploads/2013/03/toggle_button1.gif


    Lesson 24: Creating Charts and Graphs in Excel VBA

    In Excel VBA , we can write code to create charts and graphs easily based on the data on the spreadsheet. Excel VBA has made charting engine as part of the Shape object. It is also an object by itself. 燱e can create charts on

    a sheet of their own or embed them into an 爀xisting worksheet. The chart sheet is the Chart object whereas the embedded chart is part of the shape collection for the worksheet.


      1. Creating a Pie Chart


        To create a pie chart in a spreadsheet, first of all you need to enter a range of data in spreadsheet. After entering the data, you need to name the range by right-clicking the range and clicking define name in the pop-up menu. Name the chart MyChart Now insert a command button and then click it to enter the following code in the Excel VBA editor


        Private Sub CommandButton1_Click() ActiveSheet.Shapes.AddChart.Select ActiveSheet.Shapes(1).Top = 10

        ActiveSheet.Shapes(1).Left = 10 ActiveChart.ChartType = xl3DPie ActiveChart.PlotArea.Select ActiveChart.SetSourceData Source:=Range("MyChart") ActiveChart.HasTitle = True

        ActiveChart.ChartTitle.Text = "My Chart" End Sub

        image

        The Output


        Figure 24.1: Pie Chart


        To separate the pie chart into slices (The technical term is explode), you can add this line to the code: ActiveChart.SeriesCollection(1).Explosion = 10

        As there are five sectors, setting the explosion value to 10 explode all slices. You will get the following chart:


        https://99Excel.Com/wp-content/uploads/2013/12/vba2010_figure24.4.jpg


        Figure 24.2


      2. Creating a Bar Graph


    To draw a bar graph, you just need to change the ChartType to牋xl3DColumn. Private Sub CommandButton2_Click()

    ActiveSheet.Shapes.AddChart.Select ActiveSheet.Shapes(1).Top = 10

    ActiveSheet.Shapes(1).Left = 10 ActiveChart.ChartType = xl3DColumn ActiveChart.PlotArea.Select ActiveChart.SetSourceData Source:=Range("MyChart") ActiveChart.HasTitle = True

    ActiveChart.ChartTitle.Text = "My Chart" End Sub


    image


    Figure 24.3


    Excel VBA 2010 has built-in parameters to define the types of charts that can be drawn on a spreadsheet. 燳ou can refer to the parameters as the ChartType properties. The list of common properties for ChartType are listed below:



    Property


    Chart Type


    xlArea


    Area Chart


    xlBar


    Bar Chart


    xlColumn


    Column Chart


    xlLine


    Line Chart

    xlPie Pie Chart xlXYScatter XY Scatter Chart xl3DArea 3D Area Chart

    xl3DBar 3D Bar Chart


    xl3DColumn 3D Column Chart Xl3DLine 3D Line Chart


    For example, if you change the ChartType property to xlLine using the code ActiveChart.ChartType = xlLine

    you can get a line graph as follow:


    image


    Lesson 25: Creating Animation in Excel VBA


    Animation can be achieved by changing the position of an object continuously using a looping sub procedure. Two properties or functions that are required to change the positions or coordinates of the object are

    the Left and Top properties. The Left property specifies the distance of the left edge of the object in pixel from the left border of the screen and the Top property specifies the distance of the top edge of the object from the top border of the screen.


    For for example, the following code makes the object move from left to right then back to left again repeatedly until the user press the stop button. The reset button move the object back to the starting position.


    Beside creating VBA code for mathematical and financial calculations, it is also possible to creating some fun applications in Excel VBA, including games and animation. Although professionals programmers might not be interested to write such applications, it is worth while trying them out as a hobby and for personal satisfaction.


    The code:

    logo


    Private Sub StartButton_Click() repeat:


    With VBAProject.Sheet1.Image1

    .Left = .Left + 1 DoEvents

    If .Left > 200 Then .Left = 1 End With


    GoTo repeat


    End Sub


    https://99Excel.Com/vba_img/moveleft.jpg


    If you wish to move the object up and down, change the above code by replacing the property Left to Top, the code appear as follows:


    Private Sub StartButton_Click() repeat:


    With

    .Top=


    .Top+


    VBAProject.Sheet1.Image1

    1

    DoEvents

    If .Top>


    200


    Then


    .Top


    = 1

    End

    With


    GoTo repeat


    End Sub

    logo


    If you wish to make the object move diagonally, then use the properties Top and Left at the same time, as follows:

    Private Sub StartButton_Click() repeat:


    With VBAProject.Sheet1.Image1


    .Top = .Top + 5

    .Left = .Left + 5


    DoEvents

    If .Top > 200 Then .Top = 1 If .Left > 200 Then .Left = 1


    End With GoTo repeat

    End Sub